DROP PROCEDURE AA				;@
DROP PROCEDURE A				;@
DROP PROCEDURE P_RECETE_AKTAR	;@ -- "P_RECETE" DEN DAHA ONCE SLNMEL
DROP PROCEDURE P_RECETE			;@
DROP PROCEDURE R_MLYT_AKTAR  		;@ -- "R_MLYT" DEN DAHA ONCE SLNMEL
DROP PROCEDURE R_MLYT 			;@


DROP PROCEDURE P_MRP_1			;@
DROP PROCEDURE R_STOK_1			;@
DROP PROCEDURE P_RCT_AKTAR		;@
DROP PROCEDURE P_RCT			;@

DROP PROCEDURE P_MS_YOGUNLUK		;@
DROP PROCEDURE GG_TOP 			;@
DROP PROCEDURE GIDER_TOP 		;@
DROP PROCEDURE ISLEM_KODU 		;@
DROP PROCEDURE ISLEM_KODU_CARI 	;@
DROP PROCEDURE ISLEM_KODU_CK 		;@
DROP PROCEDURE KASA_AYRINTILARI 	;@
DROP PROCEDURE KASA_TOP 			;@
DROP PROCEDURE KAYIT			;@
DROP PROCEDURE KAYIT_1			;@
DROP PROCEDURE KK 				;@
DROP PROCEDURE P_AS 			;@
DROP PROCEDURE P_CARI 			;@
DROP PROCEDURE P_DATA 			;@
DROP PROCEDURE P_KASA 			;@
DROP PROCEDURE P_KURYE 			;@
DROP PROCEDURE P_PDA  			;@
DROP PROCEDURE P_PLU_NO 			;@
DROP PROCEDURE P_SON_ISLEM 		;@
DROP PROCEDURE RP_2 			;@
DROP PROCEDURE R_DEPO 			;@

DROP PROCEDURE R_URUN_TOP 		;@
DROP PROCEDURE URUN  			;@
DROP PROCEDURE CARI_TOP_X 		;@


--******************************************************************

DROP VIEW  TERAZI_AYRINTI 	;@
DROP VIEW  V_SIPARIS		;@
DROP VIEW  X 			;@

--******************************************************************

DROP TRIGGER CARILER_BI 		;@
DROP TRIGGER CK_AI		;@
DROP TRIGGER CK_AU		;@
DROP TRIGGER CK_BI		;@
DROP TRIGGER DATA_BI		;@
DROP TRIGGER DETAIL_BI		;@
DROP TRIGGER EVRAKLAR_BI		;@
DROP TRIGGER FK_AI		;@
DROP TRIGGER HEADER_AI		;@
DROP TRIGGER KULLANICILAR_BI	;@
DROP TRIGGER PDKS_BI		;@
DROP TRIGGER STOK_BI		;@
DROP TRIGGER T			;@
DROP TRIGGER URUNLER_BI		;@
DROP TRIGGER YEDEK_RAPOR_BI 	;@


--******************************************************************

alter table CARILER add constraint PK_CARILER primary key (CARI_KODU) ;@

delete from RDB$GENERATORS where RDB$GENERATOR_NAME = 'GEN_PARA';@

alter table URUNLER add X_KODU "N (BARKOD)" ;@		
CREATE INDEX IDX_URUNLER4 ON URUNLER (X_KODU) ;@


CREATE TABLE 	DIL (DIL_KODU 	"integer" NOT NULL) ;@
ALTER TABLE 	DIL ADD PROG 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD TUR 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD GRUP 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD ISIM 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD D1 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD D2 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD D3 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD D4 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD D5 	"varchar 060" COLLATE PXW_TURK ;@
ALTER TABLE 	DIL ADD D6 	"varchar 060" COLLATE PXW_TURK ;@

alter table DIL add constraint PK_DIL primary key (DIL_KODU)  ;@

CREATE INDEX IDX_DIL01 ON DIL (PROG)  	;@
CREATE INDEX IDX_DIL03 ON DIL (GRUP)  	;@
CREATE INDEX IDX_DIL04 ON DIL (ISIM)  	;@


DROP TRIGGER RPR_BI ;@
ALTER TABLE RPR DROP CONSTRAINT PK_RPR  ;@
drop index IDX_RPR01 ;@
drop index IDX_RPR02 ;@
drop index IDX_RPR03 ;@
drop index IDX_RPR04 ;@
drop index IDX_RPR05 ;@
DROP TABLE RPR ;@


CREATE TABLE 	DEPARTMAN (D_KODU 	"smalint" NOT NULL) ;@
ALTER TABLE 	DEPARTMAN ADD D_ADI 	"varchar 025" COLLATE PXW_TURK ;@
alter table DEPARTMAN add constraint PK_DEPARTMAN primary key (D_KODU)  ;@

INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (01, 'MUHTELIF GIDA')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (02, 'TEMEL GIDA')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (03, 'KURUYEMIS-I')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (04, 'KURUYEMIS-II')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (05, 'MESRUBAT')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (06, 'DONDURMA')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (07, 'KAHVE')		;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (08, 'TEKEL')		;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (09, 'BAHARAT')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (10, '')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (11, '')	;@
INSERT INTO DEPARTMAN (D_KODU, D_ADI) VALUES (12, '')	;@


alter table "SIPARISLER" drop "SUBESI" 	;@
alter table "SIPARISLER" drop "URUN_ADI" 	;@
ALTER TABLE SIPARISLER ADD ISLEM "integer" 	;@
ALTER TABLE SIPARISLER ADD DURUM "integer" 	;@
ALTER TABLE SIPARISLER ADD SUBE "varchar 025" COLLATE PXW_TURK ;@


DROP VIEW V_RESTO_FTR ;@
DROP VIEW V_YEDEK_FTR ;@
DROP PROCEDURE KAYIT_2 ;@

DROP VIEW V_MALIYET ;@
DROP PROCEDURE P_DEPO ;@
DROP PROCEDURE R_MALIYET ;@
DROP PROCEDURE R_MALIYET_1 ;@
DROP PROCEDURE R_RECETE ;@
DROP VIEW V_RECETE ;@

drop index IDX_STOK_04;@
drop index IDX_STOK_06 ;@
alter table "STOK" drop "DEPO_ADI" ;@
alter table "STOK" drop "EVRAK_TURU"  ;@
alter table "STOK" drop "ISKONTO"  ;@
--alter table "URUNLER" drop "BIRIM_FIYATI_2" ;@


--******************************************************************

CREATE TRIGGER CARILER_BI FOR CARILER ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
if(NEW.cari_kodu IS NULL) then
 begin
     if (New.kart_turu='Dier lemler') then NEW.CARI_KODU=GEN_ID(gen_gider,1);
     else if(NEW.cari_kodu IS NULL) then NEW.cari_kodu=GEN_ID(gen_cariler,1);
 end
END
;@


CREATE TRIGGER CK_AI FOR CK ACTIVE
AFTER UPDATE POSITION 0
AS
BEGIN
if (new.islem_durumu=0) then
update stok set islem_durumu=0 where cke_islem_kodu=old.islem_kodu;
END
;@


CREATE TRIGGER CK_AU FOR CK ACTIVE
AFTER DELETE POSITION 1
AS
BEGIN
if (old.islem_durumu=0) then
delete from stok  where cke_islem_kodu=old.islem_kodu;
END
;@


CREATE TRIGGER CK_BI FOR CK ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
if (new.islem_kodu is null) then  NEW.islem_kodu=GEN_ID(GEN_CK,1);

if (new.TUTAR is null)            then  NEW.TUTAR=0;
if (new.TUTAR_KURU is null)       then  NEW.TUTAR_KURU=0;
if (new.HESAP_TUTARI is null)     then  NEW.HESAP_TUTARI=0;
if (new.HESAP_TUTAR_KURU is null) then  NEW.HESAP_TUTAR_KURU=0;

new.KAYIT_TARIHI='Now';
END
;@



CREATE TRIGGER DATA_BI FOR DATA ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
if(NEW.ISLEM_KOD IS NULL) then NEW.ISLEM_KOD=GEN_ID(GEN_DATA,1);
NEW.REEL='NOW';
END
;@



CREATE TRIGGER DETAIL_BI FOR DETAIL ACTIVE
BEFORE INSERT POSITION 0

AS
BEGIN
NEW.ISLEM_NO = GEN_ID(GEN_DATA, 1);
NEW.REEL='NOW';
NEW.TARIH=current_date;
NEW.SAAT=current_time;

if(NEW.INDIRIM IS NULL)                then NEW.INDIRIM=1;
if(NEW.musteri IS NULL)                then NEW.musteri=NEW.VENDORNO;
if(NEW.KULLANICI IS NULL) then NEW.KULLANICI='Denge';

if(NEW.weight=0)        then NEW.weight=1;

if(new.PLUCLASS=0 or new.PLUCLASS=4 ) then                   /* 1=adetli 0=arlkl*/
   NEW.weight=NEW.weight/1000;
else 
   NEW.weight=new.PIECES;

if     (new.PLUNO <901)                then NEW.DEPNO=2;
else if(new.PLUNO between 901 and 910) then NEW.DEPNO=3;
else if(new.PLUNO >910)                then NEW.DEPNO=1;


if(NEW.vendorno=53 and new.cancelstatus <> 9) then
  BEGIN
  NEW.scaleno=53;
  NEW.musteri='Terazi ade';
  if(new.cancelstatus <> -11) then NEW.cancelstatus =0;
  
  /*
  NEW.ticketno=0;
  NEW.price  =NEW.price * -1;
  NEW.weight =NEW.weight * -1;
  */
  END

if(NEW.vendorno=55 and new.cancelstatus <> 9) then
  BEGIN
  NEW.scaleno=55;
  NEW.musteri='Kasa ade';
  if(new.cancelstatus <> -11) then
    NEW.cancelstatus =0;
  /*
  NEW.ticketno=0;
  NEW.price  =NEW.price * -1;
  NEW.weight =NEW.weight * -1;
  */
  END
END
;@





CREATE TRIGGER EVRAKLAR_BI FOR EVRAKLAR ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
-- if (new.islem_kodu is null) then  NEW.islem_kodu=GEN_ID(GEN_EVRAK,1);
-- ARTIK CERDEN VERLYOR KOD
END
;@


CREATE TRIGGER FK_AI FOR FK ACTIVE
AFTER UPDATE POSITION 0
AS
BEGIN
post_event 'TEL';
END
;@

CREATE TRIGGER HEADER_AI FOR HEADER ACTIVE
AFTER INSERT POSITION 0
AS
BEGIN
   INSERT INTO data
   SELECT
   a.ISLEM_NO,          /* ISLEM_KOD */
   a.PLUNO,             /* URUN_KODU */
   b.URUN_ADI,          /* URUN_ADI */
   b.URUN_TURU,         /* URUN_TURU */
   a.TICKETNO,          /* SATIS_KOD */
   (300)+(a.VENDORNO),  /* MASA_NO */
   a.musteri,           /* MASA */
   a.WEIGHT,            /* ADET */
   a.UNITPRICE,         /* BIRIM_FIYATI */
   a.PRICE,             /* TUTAR */
   1,                   /* ISLEM */
   a.KASA,              /* KASA */
   a.KULLANICI,         /* KULLANICI */
   a.SUBE,              /* SUBESI */
   a.TARIH,             /* TARIH */
   a.SAAT,              /* SAAT */
   0,                   /* HESAP */
   1,                   /* TAHSILAT */
   0,                   /* YAZICI_GRUBU */
   a.REEL,              /* REEL */
   '',                  /* ACIKLAMA */
   0,                   /* CARI_KODU */
   '',                  /* CARI */
   '',                  /* KASIYER */
   '',                  /* U_ADI_2 */
   '',                  /* U_ADI_3 */
   '',                  /* U_ADI_4 */
   (CASE WHEN a.VENDORNO=53 THEN 0 ELSE a.TICKETNO END),          /* TAH_KOD */
   '',                  /* PAKET */
   1                    /* KISI_SAYISI */
   FROM DETAIL a
   LEFT JOIN URUNLER b ON a.PLUNO=b.plu_no
   where
   a.TICKETNO = NEW.TICKETNO ;
   
    /* KONTROL CN ASAGIDAK 2 SATIR SMDLK KULLANDIRILMIYOR. TAHS
    LAT YAPINCA MANUEL SLDRYORUZ. DENEMELER SONUNDA PRONLEM
    YANI BU TABLE LARDA KALNAN YOK SE SLME SLEM BURADAN YAPILACAK- */
    
     DELETE FROM DETAIL WHERE TICKETNO = NEW.TICKETNO ; 
     DELETE FROM HEADER WHERE TICKETNO = NEW.TICKETNO ; 
    
    POST_EVENT 'NEWRECORD';
END
;@




CREATE TRIGGER KULLANICILAR_BI FOR KULLANICILAR ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
NEW.user_kodu=GEN_ID(GEN_CARILER,1);
END
;@



CREATE TRIGGER PDKS_BI FOR PDKS ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
NEW.ISLEM_KODU=GEN_ID(GEN_DATA,1);
END
;@


CREATE TRIGGER STOK_BI FOR STOK ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
NEW.islem_kodu=GEN_ID(GEN_STOK,1);
NEW.kayit_tarihi='NOW';
if (NEW.ISKNT0_1 IS NULL) THEN NEW.ISKNT0_1=0;
if (NEW.ISKNT0_2 IS NULL) THEN NEW.ISKNT0_2=0;
if (NEW.ISKNT0_3 IS NULL) THEN NEW.ISKNT0_3=0;
if (NEW.ISKNT0_4 IS NULL) THEN NEW.ISKNT0_4=0;
if (NEW.ISKNT0_5 IS NULL) THEN NEW.ISKNT0_5=0;
if (NEW.ISKNT0_6 IS NULL) THEN NEW.ISKNT0_6=0;
NEW.TIP=1;
if(NEW.islem_tipi=2 or NEW.islem_tipi=21) THEN -- Sat yada subeye sat ise stoktan '-'  cariye '+'
   BEGIN
   NEW.MIKTAR=NEW.MIKTAR*-1;
   NEW.TIP=-1;
   END
END
;@


CREATE TRIGGER T FOR DATA ACTIVE
AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
post_event 'NEWRECORT';
END
;@



CREATE TRIGGER URUNLER_BI FOR URUNLER ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF(NEW.urun_kodu IS NULL) THEN NEW.urun_kodu=GEN_ID(gen_urunler,1);
IF(NEW.plu_no IS NULL or NEW.plu_no=0)    THEN
   begin
   IF(NEW.urun_kodu<2000) then
     NEW.plu_no=NEW.urun_kodu;
   else
     NEW.plu_no=GEN_ID(gen_plu,1);
   end
END
;@




CREATE TRIGGER YEDEK_RAPOR_BI FOR YEDEK_RAPOR
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
     if(NEW.REEL IS NULL) then NEW.REEL='NOW';
END
;@


--******************************************************************



CREATE VIEW TERAZI_AYRINTI(
  ISLEM_NO,
  FIRE,
  INDIRIM,
  URUN_ADI,
  SIPARIS_YERI,
  MIKTAR_TURU,
  URUN_KODU,
  TERAZI_NO,
  FIS_NO,
  MIKTAR,
  BIRIM_FIYATI,
  TUTAR,
  SATICI_NO,
  ISLEM_DURUMU,
  TARIH,
  KASA)
AS
SELECT
  a.ISLEM_NO,
  b.FIRE,
  a.INDIRIM,
  b.URUN_ADI,
  b.SIPARIS_YERI,
  b.MIKTAR_TURU,
  a.PLUNO,
  a.SCALENO,
  a.TICKETNO,
  a.WEIGHT,
  a.UNITPRICE,
  a.PRICE,
  a.VENDORNO,
  a.CANCELSTATUS,
  a.TARIH,
  a.KASA

FROM detail a, urunler b
where a.PLUNO=b.PLU_NO
;@




CREATE VIEW V_SIPARIS(
  SIPARIS_YERI,
  ISLEM,
  URUN_KODU,
  URUN_ADI,
  MIKTAR,
  MIKTAR_TURU)
AS
SELECT
    SIPARIS_YERI,
    CAST(0 as INTEGER),
    URUN_KODU,
    URUN_ADI,
    CAST(0 as DOUBLE PRECISION),
    MIKTAR_TURU
FROM URUNLER
WHERE
    SIPARIS_YERI > 0
    and URUN_ADI IS NOT NULL
    and URUN_ADI<>''
    and URUN_KODU NOT BETWEEN 901 and 910

UNION

SELECT
    b.SIPARIS_YERI,
    a.ISLEM,
    a.URUN_KODU,
    b.URUN_ADI,
    a.MIKTAR,
    b.MIKTAR_TURU
FROM SIPARISLER a, URUNLER b
where a.urun_kodu=b.urun_kodu AND a.islem in(1,2)
;@




CREATE VIEW X (
    kisi_sayisi,
    CARI_KODU,
    TAHSILAT,
    HESAP,
    MASA_NO,
    TUTAR)
AS
SELECT
    CAST(null as INTEGER),
    0,
    TAHSILAT,
    HESAP,
    MASA_NO,
    TUTAR
FROM DATA_X
UNION
SELECT
    kisi_sayisi,
    cari_kodu,
    TAHSILAT,
    HESAP,
    MASA_NO,
    SUM(TUTAR)
FROM DATA
where
   ISLEM IN (1,9)
GROUP by
    TAHSILAT, MASA_NO, HESAP, cari_kodu, kisi_sayisi
;@




--******************************************************************
--******************************************************************
--******************************************************************



CREATE PROCEDURE P_RCT (
    D1 DATE,
    D2 DATE,
    SUBE INTEGER,
    PLU INTEGER)
RETURNS (
    PLU_NO INTEGER,
    MIKTAR1 DOUBLE PRECISION,
    MIKTAR2 DOUBLE PRECISION)
AS/**/

DECLARE VARIABLE P1 INTEGER;
DECLARE VARIABLE P2 INTEGER;

DECLARE VARIABLE RECETE_IDx INTEGER;
DECLARE VARIABLE TURx INTEGER;
DECLARE VARIABLE PLU_NOx INTEGER;
DECLARE VARIABLE MIKTARx1 DOUBLE PRECISION;
DECLARE VARIABLE MIKTARx2 DOUBLE PRECISION;

DECLARE VARIABLE TURy INTEGER;
DECLARE VARIABLE PLU_NOy INTEGER;
DECLARE VARIABLE MIKTARy1 DOUBLE PRECISION;
DECLARE VARIABLE MIKTARy2 DOUBLE PRECISION;

DECLARE VARIABLE PLU_NOz INTEGER;
DECLARE VARIABLE MIKTARz1 DOUBLE PRECISION;
DECLARE VARIABLE MIKTARz2 DOUBLE PRECISION;

BEGIN
if (PLU=0) then
begin
   p1=0;
   p2=9999;
end

else
begin
   p1=:PLU;
   p2=:PLU;
end

FOR
   SELECT x1.RECETE_ID,
          x2.urun_turu,
          x1.PLU_NO,
          sum(x1.adet),
          sum(x1.adet*(CASE WHEN x1.urun_adi like '%(1,5)%' THEN 1.5
                            WHEN x1.urun_adi like '%(Y)%' THEN 0.5
                            WHEN x1.urun_adi like '%(D)%' THEN 2.0
                            ELSE 1 END))
   FROM YEDEK_RAPOR x1, urunler x2
   WHERE x1.plu_no=x2.plu_no and (x1.TARIH between :D1 and :D2) and (x1.URUN_TURU between 0 and 2) and (x1.SUBE_ID=:SUBE) and (x1.PLU_NO between :P1 and :P2)
   group by x1.PLU_NO, x1.RECETE_ID, x2.urun_turu, x2.urun_adi
   into :RECETE_IDx, :TURx, :PLU_NOx, :MIKTARx1, :MIKTARx2
DO
BEGIN
   if(RECETE_IDx is null or RECETE_IDx=0) then  /* resto icin */
      RECETE_IDx=:PLU_NOx;

   if(:TURx=0) then /*barkodlu urunleri direk ekle*/
   begin
       PLU_NO=PLU_NOx;
       MIKTAR1=:MIKTARx1;
       MIKTAR2=:MIKTARx2;
       SUSPEND;   /* bilgiyi gonderiyor*/
   end

   else if (:TURx=1 or :TURx=2) then
   BEGIN
      FOR
         select x2.urun_turu, x1.plu_no, x1.miktar*:MIKTARx1, x1.miktar*:MIKTARx2 from receteler x1, urunler x2   /* RECETEYE BAKIYORUZ*/
         where  x1.plu_no=x2.plu_no and x1.recete_kodu=:RECETE_IDx
         into   :TURy, :PLU_NOy, :MIKTARy1, :MIKTARy2
      DO
      BEGIN
         if(:TURy=0) then
         begin
            PLU_NO=PLU_NOy;
            MIKTAR1=:MIKTARy1;
            MIKTAR2=:MIKTARy2;
            SUSPEND;   /* bilgiyi gonderiyor*/
         end

         else if (:TURy=1 or :TURy=2) then   /* alt recete ise*/
         BEGIN
            FOR
               select x1.plu_no, x1.miktar*:MIKTARy1, x1.miktar*:MIKTARy2 from receteler x1, urunler x2   /* RECETEYE BAKIYORUZ*/
               where  x1.plu_no=x2.plu_no and x1.recete_kodu=:PLU_NOy
               into   :PLU_NOz, :MIKTARz1, :MIKTARz2
            DO
            begin
               PLU_NO=PLU_NOz;
               MIKTAR1=:MIKTARz1;
               MIKTAR2=:MIKTARz2;
               SUSPEND;   /* bilgiyi gonderiyor*/
            end
         END
      END
   END
END
END
;@





CREATE PROCEDURE P_RCT_AKTAR (
    D1 DATE,
    D2 DATE,
    SUBE INTEGER,
    PLU INTEGER)
RETURNS (
    ISLEM_TARIHI DATE,
    URUN_KODU NUMERIC (18, 0),
    PARA_BIRIMI VARCHAR (25) CHARACTER SET WIN1254,
    FIYAT DOUBLE PRECISION,
    KUR DOUBLE PRECISION,
    KDV SMALLINT,
    MIKTAR DOUBLE PRECISION,
    ISLEM_TIPI SMALLINT,
    ISLEM_DURUMU SMALLINT,
    EVRAK_NO VARCHAR (25) CHARACTER SET WIN1254,
    GARANTI SMALLINT,
    SERI_NO VARCHAR (25) CHARACTER SET WIN1254,
    KULLANICI VARCHAR (25) CHARACTER SET WIN1254,
    ACIKLAMA VARCHAR (200) CHARACTER SET WIN1254,
    KAYIT_TARIHI TIMESTAMP,
    ISLEM_KODU INTEGER,
    CARI_KODU INTEGER,
    CKE_ISLEM_KODU INTEGER,
    ISKNT0_1 DOUBLE PRECISION,
    ISKNT0_2 DOUBLE PRECISION,
    TIP SMALLINT,
    ISKNT0_3 DOUBLE PRECISION,
    ISKNT0_4 DOUBLE PRECISION,
    ISKNT0_5 DOUBLE PRECISION,
    ISKNT0_6 DOUBLE PRECISION,
    SUBE_KODU INTEGER,
    PLU_NO INTEGER)
AS/**/
DECLARE VARIABLE ORT DOUBLE PRECISION;
DECLARE VARIABLE A DOUBLE PRECISION;
BEGIN

ISLEM_TARIHI=:D1;
URUN_KODU=0;
PARA_BIRIMI='YTL';
--FIYAT=0;                          (asadan geliyor)
KUR=1;
KDV=0;
--MIKTAR                            (asadan geliyor)
ISLEM_TIPI=2003; --  SATI (-)
ISLEM_DURUMU=1;
EVRAK_NO=:D1;
GARANTI=0;
SERI_NO=0;
KULLANICI=USER;
ACIKLAMA='';
--KAYIT_TARIHI                      (otomatik)
--ISLEM_KODU                        (otomatik)
CARI_KODU=:SUBE;
CKE_ISLEM_KODU=0;
--ISKNT0_1                          (bos ise otomatik)
--ISKNT0_2                          (bos ise otomatik)
--TIP                                      (otomatik)
--ISKNT0_3                          (bos ise otomatik)
--ISKNT0_4                          (bos ise otomatik)
--ISKNT0_5                          (bos ise otomatik)
--ISKNT0_6                          (bos ise otomatik)
SUBE_KODU=:SUBE;

FOR SELECT
   a.PLU_NO,
   Sum(MIKTAR2*-1),
   (select  avg((miktar*fiyat)/miktar) from stok where (islem_tarihi between current_date-7 and current_date) and plu_no=a.plu_no and islem_tipi=-2),
   b.A
FROM
   P_RCT (:D1,:D2,:SUBE,:PLU)a, urunler b
where
   a.plu_no=b.plu_no
GROUP BY
   a.PLU_NO, b.A
HAVING
   Sum(MIKTAR2) IS NOT NULL
INTO :PLU_NO,:MIKTAR, :ort, :A
   DO
   begin
   FIYAT=ORT;
   IF(ORT=0 or ort is null ) THEN FIYAT=A;

   SUSPEND ;
   end

END
;@






CREATE PROCEDURE R_STOK_1 (
    D2 DATE,
    DEPO INTEGER,
    SUBESI VARCHAR (50) CHARACTER SET WIN1254)
RETURNS (
    PLU_NO INTEGER,
    URUN_ADI VARCHAR (100) CHARACTER SET WIN1254,
    MIKTAR_TURU VARCHAR (25) CHARACTER SET WIN1254,
    MIKTAR DOUBLE PRECISION,
    SAYIM DOUBLE PRECISION,
    FARK DOUBLE PRECISION,
    MI INTEGER)
AS/**/
BEGIN
--   +2003=RESTO'dan satlanlar 'SATI' stokj ckyor
--   -2003=RESTO'dan direk giriler 'ALIM' stok giriyor
--   -2004=Saym 'ALIM' stook giriyor (SLEM DURUMU =0 SE GECC)

if (SUBESI='MERKEZ') then
BEGIN
   FOR select
       b.PLU_NO,
       b.URUN_ADI,
       b.MIKTAR_TURU,
       sum(a.MIKTAR),
       CAST(0 as DOUBLE PRECISION),
       CAST(0 as DOUBLE PRECISION),
       CAST(0 as integer)
       from STOK a, URUNLER b
       where a.ISLEM_TARIHI <=:D2 AND a.ISLEM_DURUMU=1 AND a.PLU_NO=b.PLU_NO AND a.ISLEM_TIPI in (2,-2,21,-21)
       group by b.plu_no ,b.URUN_ADI, b.MIKTAR_TURU
   INTO :plu_no,:URUN_ADI,:MIKTAR_TURU,:MIKTAR,:SAYIM,:FARK,:MI DO SUSPEND ;
END


ELSE
BEGIN
   FOR select
       b.PLU_NO,
       b.URUN_ADI,
       b.MIKTAR_TURU,
       sum(a.MIKTAR)*-1,
       CAST(0 as DOUBLE PRECISION),
       CAST(0 as DOUBLE PRECISION),
       CAST(0 as integer)
       from STOK a, URUNLER b
       where a.CARI_KODU=:DEPO AND a.ISLEM_TARIHI <=:D2 AND a.ISLEM_DURUMU=1 AND a.PLU_NO=b.PLU_NO AND a.ISLEM_TIPI IN (-21,21,22)
       group by b.plu_no, b.URUN_ADI, b.MIKTAR_TURU
   INTO :plu_no, :URUN_ADI, :MIKTAR_TURU, :MIKTAR, :SAYIM, :FARK, :MI DO SUSPEND ;
END


FOR select
    b.PLU_NO,
    b.URUN_ADI,
    b.MIKTAR_TURU,
    sum(a.MIKTAR),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as integer)
    from STOK a, URUNLER b
    where a.CARI_KODU=:DEPO AND a.ISLEM_TARIHI <=:D2 AND a.ISLEM_DURUMU=1 AND a.PLU_NO=b.PLU_NO AND a.ISLEM_TIPI in (2003,-2003,-2004)
    group by b.plu_no,a.URUN_KODU,b.URUN_ADI,b.MIKTAR_TURU
INTO :plu_no, :URUN_ADI, :MIKTAR_TURU, :MIKTAR, :SAYIM, :FARK, :MI DO SUSPEND ;
   
   
FOR select    --saym degerleri
    b.PLU_NO,
    b.URUN_ADI,
    b.MIKTAR_TURU,
    CAST(0 as DOUBLE PRECISION),
    sum(a.FIYAT),
    sum(a.MIKTAR),
    count(b.URUN_KODU)
    from STOK a, URUNLER b
    where a.CARI_KODU=:DEPO AND a.ISLEM_DURUMU=0 AND a.PLU_NO=b.PLU_NO AND a.ISLEM_TIPI=-2004
    group by b.plu_no, b.URUN_ADI, b.MIKTAR_TURU
INTO :plu_no, :URUN_ADI, :MIKTAR_TURU, :MIKTAR, :SAYIM, :FARK, :MI DO SUSPEND ;


FOR select    -- her urunun Bo hali
    PLU_NO,
    URUN_ADI,
    MIKTAR_TURU,
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as integer)
    from URUNLER
    where urun_turu =0 
INTO :plu_no, :URUN_ADI, :MIKTAR_TURU, :MIKTAR, :SAYIM, :FARK, :MI DO SUSPEND ;

END
;@





CREATE PROCEDURE P_MRP_1 (
    DX0  DATE,
    DX1  DATE,
    DX2  DATE,
    DX11 DATE,
    DX22 DATE)
RETURNS (
    IDX NUMERIC (13, 0),
    MTX VARCHAR (10) CHARACTER SET WIN1254,
    STOKX DOUBLE PRECISION,
    BUGUNX DOUBLE PRECISION,
    UCGUNX DOUBLE PRECISION,
    SATISX DOUBLE PRECISION)
AS
--DECLARE VARIABLE DX0  DATE;
BEGIN

  FOR SELECT
    plu_no,
    SUM(MIKTAR),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION)
  FROM        R_STOK_1(:DX0,10001,'MERKEZ')
  GROUP BY    plu_no,URUN_ADI
  INTO        :IDX,:STOKX,:BUGUNX,:UCGUNX,:SATISX DO SUSPEND ;

  FOR SELECT
    plu_no,
    CAST(0 as DOUBLE PRECISION),
    Sum(MIKTAR1*-1),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION)
  FROM          P_RCT (:DX0,:DX0,10001,0)
  GROUP BY      plu_no
  HAVING        Sum(MIKTAR1) IS NOT NULL
  INTO          :IDX,:STOKX,:BUGUNX,:UCGUNX,:SATISX  DO SUSPEND ;

  FOR SELECT
    plu_no,
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION),
    Sum(MIKTAR1*-1),
    CAST(0 as DOUBLE PRECISION)
  FROM          P_RCT (:DX0,:DX0,10001,0)
  GROUP BY      plu_no
  HAVING        Sum(MIKTAR1) IS NOT NULL
  INTO          :IDX,:STOKX,:BUGUNX,:UCGUNX,:SATISX  DO SUSPEND ;

  FOR SELECT
    plu_no,
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION),
    CAST(0 as DOUBLE PRECISION),
    Sum(MIKTAR1*-1)
  FROM          P_RCT (:DX0,:DX0,10001,0)
  GROUP BY      plu_no
  HAVING        Sum(MIKTAR1) IS NOT NULL
  INTO          :IDX,:STOKX,:BUGUNX,:UCGUNX,:SATISX  DO SUSPEND ;

END
;@






CREATE PROCEDURE GG_TOP (
    IN_BR VARCHAR (25) CHARACTER SET WIN1254,
    AY INTEGER,
    IN_CARI INTEGER)
RETURNS (
    ISLEM_TIP VARCHAR (50) CHARACTER SET WIN1254,
    ISLEM_TIPI INTEGER,
    BR VARCHAR (25) CHARACTER SET WIN1254,
    TL DOUBLE PRECISION)
AS
  DECLARE VARIABLE d11 DOUBLE PRECISION;
  DECLARE VARIABLE d22 DOUBLE PRECISION;
  DECLARE VARIABLE d33 DOUBLE PRECISION;
  DECLARE VARIABLE d44 DOUBLE PRECISION;
  DECLARE VARIABLE d55 DOUBLE PRECISION;
  DECLARE VARIABLE kur DOUBLE PRECISION;
BEGIN

SELECT kur  from doviz_kurlari where SIRA=1  INTO :d11 ;
SELECT kur  from doviz_kurlari where SIRA=2  INTO :d22 ;
SELECT kur  from doviz_kurlari where SIRA=3  INTO :d33 ;
SELECT kur  from doviz_kurlari where SIRA=4  INTO :d44 ;
SELECT kur  from doviz_kurlari where SIRA=5  INTO :d55 ;
SELECT kur  from doviz_kurlari where para=:IN_BR  INTO :kur ;


IF (:AY=0) then
BEGIN
FOR SELECT
    (select ISIM from cariler c where c.cari_kodu=a.islem_tipi),
    ISLEM_TIPI,
    :IN_BR,
    Sum((D1*:d11+D2*:d22+D3*:d33+D4*:d44+D5*:d55)/:kur)
    FROM CK a
    WHERE
      a.CARI_KODU=:IN_CARI and
      (a.islem_tipi BETWEEN -50 AND 50) and
      ISLEM_DURUMU in(1, 2)
    GROUP BY
      ISLEM_TIPI
INTO :ISLEM_TIP, :ISLEM_TIPI, :BR, :TL   DO  SUSPEND;
END

ELSE IF(:AY>0) THEN
BEGIN
FOR SELECT
    (select ISIM from cariler c where c.cari_kodu=a.islem_tipi),
    ISLEM_TIPI,
    :IN_BR,
    Sum((D1*:d11+D2*:d22+D3*:d33+D4*:d44+D5*:d55)/:kur)
    FROM CK a
    WHERE
      a.CARI_KODU=:IN_CARI and
      (a.islem_tipi BETWEEN -50 AND 50) and
      ISLEM_DURUMU in(1, 2) and
      EXTRACT(MONTH FROM a.ISLEM_TARIHI)=:AY
    GROUP BY
      ISLEM_TIPI
INTO :ISLEM_TIP, :ISLEM_TIPI, :BR, :TL   DO  SUSPEND;
END

FOR SELECT
    ISIM,
    CAST(CARI_KODU as smallint),
    :IN_BR,
    CAST(0 as DOUBLE PRECISION)
    FROM cariler
    WHERE CARI_KODU BETWEEN -50 AND 50
INTO :ISLEM_TIP, :ISLEM_TIPI, :BR, :TL   DO  SUSPEND;

END
;@



CREATE PROCEDURE GIDER_TOP (
    AY INTEGER)
RETURNS (
    GIDER_KODU2 INTEGER,
    GIDER_ADI2 VARCHAR (100) CHARACTER SET WIN1254,
    GIDER_GRUBU2 VARCHAR (100) CHARACTER SET WIN1254,
    TL2 DOUBLE PRECISION)
AS
BEGIN
  IF (:AY>0) THEN
     BEGIN
          FOR
             SELECT
             CAST(a.ISLEM_TIPI as INTEGER),
             b.ISIM,
             b.UNVAN,                       /* CHARACTER SET WIN1254  */
             SUM(a.TUTAR)
             FROM CK a, CARILER b
             WHERE
             (ISLEM_TIPI BETWEEN 9000 AND 9999) AND
             a.ISLEM_TIPI=b.CARI_KODU and a.ISLEM_DURUMU=1 AND EXTRACT(MONTH FROM a.ISLEM_TARIHI)=:AY
             GROUP BY b.ISIM, b.UNVAN, a.ISLEM_TIPI

          UNION

             SELECT
             CARI_KODU,
             ISIM,
             UNVAN,
             CAST(0 as DOUBLE PRECISION)
             FROM CARILER
             WHERE
             (CARI_KODU BETWEEN 9000 AND 9999)
             INTO :GIDER_KODU2, :GIDER_ADI2, :GIDER_GRUBU2, :TL2
          DO
          SUSPEND ;
     END

  else if (:AY=0) THEN
     BEGIN
          FOR
             SELECT
             CAST(a.ISLEM_TIPI as INTEGER),
             b.ISIM,
             b.UNVAN,
             SUM(a.TUTAR)
             FROM CK a, CARILER b
             WHERE
             (ISLEM_TIPI BETWEEN 9000 AND 9999) AND
             a.ISLEM_TIPI=b.CARI_KODU and a.ISLEM_DURUMU=1 and a.ISLEM_TARIHI is not null
             GROUP BY b.ISIM, b.UNVAN, a.ISLEM_TIPI

          UNION

             SELECT
             CARI_KODU,
             ISIM,
             UNVAN,
             CAST(0 as DOUBLE PRECISION)
             FROM CARILER
             WHERE
             (CARI_KODU BETWEEN 9000 AND 9999)
             INTO :GIDER_KODU2, :GIDER_ADI2, :GIDER_GRUBU2, :TL2
          DO
          SUSPEND ;
     END
END
;@




CREATE PROCEDURE ISLEM_KODU 
RETURNS (
    KOD INTEGER)
AS
BEGIN
KOD   = GEN_ID(GEN_DATA,1);
END
;@




CREATE PROCEDURE ISLEM_KODU_CARI 
RETURNS (
    KOD INTEGER)
AS
BEGIN
KOD   = GEN_ID(GEN_CARILER,1);
END
;@



CREATE PROCEDURE ISLEM_KODU_CK 
RETURNS (
    KOD INTEGER)
AS
BEGIN
KOD   = GEN_ID(GEN_CK,1);
END
;@




CREATE PROCEDURE KASA_AYRINTILARI (
    KS VARCHAR (50) CHARACTER SET WIN1254)
RETURNS (
    SIRA SMALLINT,
    TUTAR_BIRIMI VARCHAR (25) CHARACTER SET WIN1254,
    TUTAR DOUBLE PRECISION)
AS
DECLARE VARIABLE S SMALLINT;
BEGIN
S=0;
     FOR
          SELECT
            :S,
            TUTAR_BIRIMI,
            SUM(TUTAR)
          FROM CK a
          WHERE TUTAR_BIRIMI IS NOT NULL and
                KASA_ISMI=:KS and
                ISLEM_DURUMU > 0 and
                ISLEM_TIPI IN (10,11,12,13,14,-10,-11,-12,-13,-14,4,-4,5,-5)
          GROUP BY TUTAR_BIRIMI

          UNION

          SELECT
             SIRA,
             PARA,
             CAST(0 as DOUBLE PRECISION)
             FROM doviz_kurlari

             INTO :SIRA, :TUTAR_BIRIMI, :TUTAR
     DO
     SUSPEND ;
END
;@




CREATE PROCEDURE KASA_TOP (
    KRT SMALLINT,
    VADE DATE)
RETURNS (
    SIRA SMALLINT,
    TUTAR_BIRIMI VARCHAR (25) CHARACTER SET WIN1254,
    EKSI DOUBLE PRECISION,
    ARTI DOUBLE PRECISION,
    TUTAR DOUBLE PRECISION,
    ISLEM_TARIHI DATE,
    TOPLAM DOUBLE PRECISION)
AS
DECLARE VARIABLE S SMALLINT;
BEGIN
 S=0;
 
IF(KRT=0) THEN
 begin
 FOR SELECT
   :S,
   TUTAR_BIRIMI,
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR <0 and ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi is not null),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR >0 and ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi is not null),
   (SELECT SUM(TUTAR) FROM CK WHERE              ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi is not null),
   (SELECT SUM(TUTAR) FROM CK WHERE                                     TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi is not null)
   FROM CK a
   WHERE TUTAR_BIRIMI IS NOT NULL and ISLEM_DURUMU > 0 and ISLEM_TIPI IN (40,50,10,11,12,13,14,-10,-11,-12,-13,-14,-50) and kasa_ismi is not null
   GROUP BY TUTAR_BIRIMI
 INTO :SIRA, :TUTAR_BIRIMI, :EKSI, :ARTI, :TUTAR, :TOPLAM     DO     SUSPEND ;
 
   FOR SELECT
   SIRA,
   PARA,
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION)
   FROM doviz_kurlari
 INTO :SIRA, :TUTAR_BIRIMI, :EKSI, :ARTI, :TUTAR, :TOPLAM     DO     SUSPEND ;
 end

IF(KRT=1) THEN
 begin
 FOR SELECT
   :S,
   TUTAR_BIRIMI,
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR <0 and ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi='Merkez Kasa'),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR >0 and ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi='Merkez Kasa'),
   (SELECT SUM(TUTAR) FROM CK WHERE              ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi='Merkez Kasa'),
   (SELECT SUM(TUTAR) FROM CK WHERE                                     TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi='Merkez Kasa')
   FROM CK a
   WHERE TUTAR_BIRIMI IS NOT NULL and ISLEM_DURUMU > 0 and ISLEM_TIPI IN (40,50,10,11,12,13,14,-10,-11,-12,-13,-14,-50) and kasa_ismi='Merkez Kasa'
   GROUP BY TUTAR_BIRIMI
 INTO :SIRA, :TUTAR_BIRIMI, :EKSI, :ARTI, :TUTAR, :TOPLAM     DO     SUSPEND ;

   FOR SELECT
   SIRA,
   PARA,
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION)
   FROM doviz_kurlari
 INTO :SIRA, :TUTAR_BIRIMI, :EKSI, :ARTI, :TUTAR, :TOPLAM     DO     SUSPEND ;
 end
 
 IF(KRT=2) THEN
 begin
 FOR SELECT
   :S,
   TUTAR_BIRIMI,
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR <0 and ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi<>'Merkez Kasa'),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR >0 and ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi<>'Merkez Kasa'),
   (SELECT SUM(TUTAR) FROM CK WHERE              ISLEM_VADESI=:VADE and TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi<>'Merkez Kasa'),
   (SELECT SUM(TUTAR) FROM CK WHERE                                     TUTAR_BIRIMI=a.TUTAR_BIRIMI and kasa_ismi<>'Merkez Kasa')
   FROM CK a
   WHERE TUTAR_BIRIMI IS NOT NULL and ISLEM_DURUMU > 0 and ISLEM_TIPI IN (40,50,10,11,12,13,14,-10,-11,-12,-13,-14,-50) and kasa_ismi<>'Merkez Kasa'
   GROUP BY TUTAR_BIRIMI
 INTO :SIRA, :TUTAR_BIRIMI, :EKSI, :ARTI, :TUTAR, :TOPLAM     DO     SUSPEND ;

   FOR SELECT
   SIRA,
   PARA,
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION),
   CAST(0 as DOUBLE PRECISION)
   FROM doviz_kurlari
 INTO :SIRA, :TUTAR_BIRIMI, :EKSI, :ARTI, :TUTAR, :TOPLAM     DO     SUSPEND ;
 end

END
;@



CREATE PROCEDURE KAYIT (
    S_KODU INTEGER)
AS/**/
BEGIN
	/*INSERT INTO YEDEK_RAPOR select * FROM V_DENGE WHERE TAH_KOD=:S_KODU;*/
	INSERT INTO YEDEK_RAPOR 
        SELECT
           a.PLUNO,
           b.URUN_ADI,
           a.DEPNO,
           a.TARIH,
           a.CANCELSTATUS,
           a.KULLANICI,
           CAST('Market' as VARCHAR (25) ),
           a.SUBE,
           a.KASA,
           CAST('Market' as VARCHAR (60) ),
           CAST(0 AS INTEGER),
           CAST(a.KULLANICI as VARCHAR (25) ),
           a.WEIGHT,
           a.PRICE,
           a.VENDORNO,
           a.TICKETNO,
           a.SAAT,
           b.URUN_ADI,
           b.GRUP1,
           b.GRUP2,
           b.GRUP3,
           CAST('' as VARCHAR (250)),
           b.PLU_NO,
           b.RECETE_ID,
           a.REEL,
           a.ISLEM_NO,
           a.TICKETNO,
           CAST(10001 AS INTEGER),   /* sube */
           CAST(1 AS INTEGER)        /* kisisayisi */
        FROM DETAIL a
        LEFT JOIN URUNLER b ON a.PLUNO=b.plu_no
        where
           TICKETNO=:S_KODU AND a.CANCELSTATUS = 2 ;
	
    DELETE FROM DETAIL WHERE CANCELSTATUS=2 and TICKETNO=:S_KODU;

END
;@



CREATE PROCEDURE KAYIT_1 (
    S_KODU INTEGER)
AS/* */
BEGIN
	/* INSERT INTO YEDEK_RAPOR select * FROM V_RP WHERE tah_kod=:S_KODU; */
	INSERT INTO YEDEK_RAPOR
           SELECT
           a.URUN_KODU,
           a.URUN_ADI,
           a.URUN_TURU,
           a.TARIH,
           a.ISLEM,
           a.KULLANICI,
           a.MASA,
           a.SUBESI,
           a.KASA,
           a.CARI,
           a.CARI_KODU,
           a.KASIYER,
           a.ADET,
           a.TUTAR,
           a.MASA_NO,
           a.TAH_KOD,
           a.SAAT,
           a.URUN_ADI,
           b.GRUP1,
           b.GRUP2,
           b.GRUP3,
           a.ACIKLAMA,
           b.PLU_NO,
           b.RECETE_ID,
           a.REEL,
           a.ISLEM_KOD,
           a.SATIS_KOD,
           CAST(10001 AS INTEGER), 		/* sube */
           a.KISI_SAYISI			/* kisisayisi */
    		
           FROM DATA a
           LEFT JOIN URUNLER b ON
           a.URUN_KODU=b.URUN_KODU
           where  tah_kod=:S_KODU and a.ISLEM=2 ;
 
    DELETE FROM DATA WHERE islem=2 and tah_kod=:S_KODU;
END
;@




CREATE PROCEDURE KK (
       KRT SMALLINT,
       VADE DATE
       )
RETURNS (
    KASA_ISMI VARCHAR (50) CHARACTER SET WIN1254,
    D1 DOUBLE PRECISION,
    D2 DOUBLE PRECISION,
    D3 DOUBLE PRECISION,
    D4 DOUBLE PRECISION,
    D5 DOUBLE PRECISION)
AS
BEGIN

IF(KRT=0) THEN
 begin
  FOR SELECT
   KASA_ISMI,
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=1)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=2)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=3)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=4)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=5)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI)
   FROM KASALAR a
   WHERE ISLEM=0
   ORDER BY KASA_KODU
  INTO :KASA_ISMI, :D1, :D2, :D3, :D4, :D5     DO     SUSPEND ;
 end

IF(KRT=1) THEN
 begin
  FOR SELECT
   KASA_ISMI,
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=1)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi <= :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=2)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi <= :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=3)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi <= :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=4)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi <= :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=5)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi <= :VADE)
   FROM KASALAR a
   WHERE ISLEM=0
   ORDER BY KASA_KODU
  INTO :KASA_ISMI, :D1, :D2, :D3, :D4, :D5     DO     SUSPEND ;
 end
 
IF(KRT=2) THEN
 begin
  FOR SELECT
   KASA_ISMI,
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=1)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi > :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=2)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi > :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=3)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi > :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=4)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi > :VADE),
   (SELECT SUM(TUTAR) FROM CK WHERE TUTAR_BIRIMI=(SELECT PARA FROM DOVIZ_KURLARI WHERE SIRA=5)
    and ISLEM_DURUMU > 0 and ISLEM_TIPI < 10000 AND KASA_ISMI=a.KASA_ISMI and islem_vadesi > :VADE)
   FROM KASALAR a
   WHERE ISLEM=0
   ORDER BY KASA_KODU
  INTO :KASA_ISMI, :D1, :D2, :D3, :D4, :D5     DO     SUSPEND ;
 end
 
END
;@




CREATE PROCEDURE P_AS (
    YENI_OR_EDIT SMALLINT,
    AS_ISLEM_KODU INTEGER,
    AS_CARI_KODU INTEGER,
    AS_ISLEM_TARIHI TIMESTAMP,
    AS_EVRAK_TURU VARCHAR (25) CHARACTER SET WIN1254,
    AS_EVRAK_NO VARCHAR (25) CHARACTER SET WIN1254,
    AS_ISLEM_TIPI SMALLINT,
    AS_HESAP_TUTARI DOUBLE PRECISION,
    AS_HESAP_TUTAR_BIRIMI VARCHAR (25) CHARACTER SET WIN1254,
    AS_HESAP_TUTAR_KURU DOUBLE PRECISION,
    AS_ISLEM_DURUMU SMALLINT,
    AS_ISLEM_VADESI TIMESTAMP,
    AS_ACIKLAMA VARCHAR (250) CHARACTER SET WIN1254,
    AS_SUBE_KODU INTEGER)

RETURNS (
    AS_IK INTEGER)

AS /**/
DECLARE VARIABLE YEREL_ISLEM_KODU INTEGER;
DECLARE VARIABLE KART VARCHAR (25) CHARACTER SET WIN1254;
BEGIN

if (AS_ISLEM_TIPI < 0) then AS_HESAP_TUTARI=AS_HESAP_TUTARI*-1;

	YEREL_ISLEM_KODU = GEN_ID(GEN_CK,1);    /* Sradaki lem kodunu alyoruz ...23rfaergfhlashgfkjskjfkjsf*/
    AS_IK=:YEREL_ISLEM_KODU;
    
	INSERT INTO CK
       (
       ISLEM_KODU,                   /* CHARACTER SET WIN1254 */
       CARI_KODU,
       ISLEM_TARIHI,
       EVRAK_TURU,
       EVRAK_NO,       	
       ISLEM_TIPI,
       HESAP_TUTARI,
       HESAP_TUTAR_BIRIMI,
       HESAP_TUTAR_KURU,
       ISLEM_DURUMU,
       ISLEM_VADESI,
       KAYIT_TARIHI,
       FATURA_CARI_KODU,
       ACIKLAMA,
       SUBE_KODU
       )
	VALUES
       (
       :YEREL_ISLEM_KODU,
       :AS_CARI_KODU,
       :AS_ISLEM_TARIHI,
       :AS_EVRAK_TURU,
       :AS_EVRAK_NO,
	   :AS_ISLEM_TIPI,
	   :AS_HESAP_TUTARI,
	   :AS_HESAP_TUTAR_BIRIMI,
	   :AS_HESAP_TUTAR_KURU,
       :AS_ISLEM_DURUMU,                   /* "durum=1" -> ilem geerli  */
	   :AS_ISLEM_VADESI,
	   'NOW',
	   0,
	   :AS_ACIKLAMA,
       :AS_SUBE_KODU
	   );
	
	IF(:YENI_OR_EDIT=2) THEN            /* Eer lem "EDIT" ise ...*/
	    BEGIN
          UPDATE CK   SET ISLEM_DURUMU=0, ACIKLAMA=' * EDIT *' WHERE ISLEM_KODU    =:AS_ISLEM_KODU;  /* Asl kayt iptal...*/
		  UPDATE STOK SET ISLEM_DURUMU=0, ACIKLAMA=' * EDIT *' WHERE CKE_ISLEM_KODU=:AS_ISLEM_KODU;  /* Asl kaytlar iptal...*/
	    END
	    
	UPDATE STOK SET                          /* "GEC'yi 'AS' ilemine gre gncelliyoruz ...*/
        CKE_ISLEM_KODU=:YEREL_ISLEM_KODU,
        ISLEM_DURUMU=1,
        ISLEM_TIPI=:AS_ISLEM_TIPI
        WHERE CKE_ISLEM_KODU IS NULL;
END
;@





CREATE PROCEDURE P_CARI (
    ARA VARCHAR (100) CHARACTER SET WIN1254,
    KRT SMALLINT)
RETURNS (
    CARI_KODU INTEGER,
    ISIM VARCHAR (50) CHARACTER SET WIN1254,
    UNVAN VARCHAR (200) CHARACTER SET WIN1254,
    ADRES VARCHAR (200) CHARACTER SET WIN1254,
    SEMT VARCHAR (50) CHARACTER SET WIN1254,
    SEHIR VARCHAR (50) CHARACTER SET WIN1254,
    ULKE VARCHAR (50) CHARACTER SET WIN1254,
    POSTA_KODU VARCHAR (25) CHARACTER SET WIN1254,
    TEL_1 VARCHAR (25) CHARACTER SET WIN1254,
    TEL_2 VARCHAR (25) CHARACTER SET WIN1254,
    TEL_3 VARCHAR (25) CHARACTER SET WIN1254,
    TEL_4 VARCHAR (25) CHARACTER SET WIN1254,
    FAX VARCHAR (25) CHARACTER SET WIN1254,
    MOBIL VARCHAR (25) CHARACTER SET WIN1254,
    VD VARCHAR (50) CHARACTER SET WIN1254,
    VDNO VARCHAR (25) CHARACTER SET WIN1254,
    MAIL VARCHAR (50) CHARACTER SET WIN1254,
    WEB VARCHAR (50) CHARACTER SET WIN1254,
    KULLANICI VARCHAR (50) CHARACTER SET WIN1254,
    KART_TURU VARCHAR (25) CHARACTER SET WIN1254,
    KART_DURUMU VARCHAR (25) CHARACTER SET WIN1254,
    ACIKLAMA VARCHAR (200) CHARACTER SET WIN1254,
    HESAP_BIRIMI VARCHAR (25) CHARACTER SET WIN1254,
    KATAGORI CHAR (1) CHARACTER SET WIN1254,
    YUZDE SMALLINT,
    ADRES_2 VARCHAR (200) CHARACTER SET WIN1254,
    OZEL VARCHAR (25) CHARACTER SET WIN1254,
    ILGILI VARCHAR (25) CHARACTER SET WIN1254,
    RISK DOUBLE PRECISION,
    TUTAR DOUBLE PRECISION,
    KART_ID VARCHAR (25) CHARACTER SET WIN1254)

AS /**/
DECLARE VARIABLE MYSTR VARCHAR(1024) CHARACTER SET WIN1254;
BEGIN

IF(:ARA IS NOT NULL ) then
BEGIN
     MYSTR='SELECT * FROM CARILER';
     if (KRT=0) then
     MYSTR=MYSTR || ' WHERE CARI_KODU=' || :ARA ;
     ELSE if (KRT=1) then
     MYSTR=MYSTR || ' WHERE ISIM LIKE ''' || :ARA || '''' ;
     ELSE if (KRT=2) then
          BEGIN
          MYSTR=MYSTR || ' WHERE ';
          MYSTR=MYSTR || ' TEL_1 LIKE ''' || :ARA || ''' OR';
          MYSTR=MYSTR || ' TEL_2 LIKE ''' || :ARA || ''' OR';
          MYSTR=MYSTR || ' TEL_3 LIKE ''' || :ARA || ''' OR';
          MYSTR=MYSTR || ' TEL_4 LIKE ''' || :ARA || ''' OR';
          MYSTR=MYSTR || ' FAX   LIKE ''' || :ARA || ''' OR';
          MYSTR=MYSTR || ' MOBIL LIKE ''' || :ARA || '''';
          END

   FOR EXECUTE STATEMENT MYSTR
   INTO
   :CARI_KODU ,
   :ISIM,
   :UNVAN,
   :ADRES,
   :SEMT,
   :SEHIR,
   :ULKE,
   :POSTA_KODU,
   :TEL_1,
   :TEL_2,
   :TEL_3,
   :TEL_4,
   :FAX,
   :MOBIL,
   :VD,
   :VDNO,
   :MAIL,
   :WEB,
   :KULLANICI,
   :KART_TURU,
   :KART_DURUMU,
   :ACIKLAMA,
   :HESAP_BIRIMI,
   :KATAGORI,
   :YUZDE,
   :ADRES_2,
   :OZEL,
   :ILGILI,
   :RISK,
   :TUTAR,
   :KART_ID
   
   DO
   SUSPEND ;
END
END
;@





CREATE PROCEDURE P_DATA (
    MN INTEGER)
RETURNS (
    ISLEM_KOD INTEGER,
    URUN_KODU NUMERIC (18, 0),
    TOPLAM DOUBLE PRECISION,
    ACIKLAMA VARCHAR (1000) CHARACTER SET WIN1254,
    URUN_ADI VARCHAR (100) CHARACTER SET WIN1254,
    U_ADI_2 VARCHAR (100) CHARACTER SET WIN1254,
    U_ADI_3 VARCHAR (100) CHARACTER SET WIN1254,
    U_ADI_4 VARCHAR (100) CHARACTER SET WIN1254,
    URUN_TURU SMALLINT,
    SATIS_KOD INTEGER,
    MASA_NO INTEGER,
    MASA VARCHAR (25) CHARACTER SET WIN1254,
    ADET DOUBLE PRECISION,
    BIRIM_FIYATI DOUBLE PRECISION,
    TUTAR DOUBLE PRECISION,
    ISLEM SMALLINT,
    KULLANICI VARCHAR (50) CHARACTER SET WIN1254,
    TARIH DATE,
    SAAT TIME,
    HESAP SMALLINT,
    TAHSILAT SMALLINT,
    YAZICI_GRUBU SMALLINT,
    CARI_KODU INTEGER,
    CARI VARCHAR (60) CHARACTER SET WIN1254,
    KASA VARCHAR (50) CHARACTER SET WIN1254,
    PAKET VARCHAR (250) CHARACTER SET WIN1254,
    TAH_KOD INTEGER)
AS/**/
DECLARE VARIABLE MYVAR  DOUBLE PRECISION;
DECLARE VARIABLE MYSTR  VARCHAR (250) CHARACTER SET WIN1254;
DECLARE VARIABLE SON SMALLINT;
DECLARE VARIABLE temp1 INTEGER; /* kullanlmyor. Cesni tekrarn onlemek iin deger gecirme amacl kullanlyor */
BEGIN
SON=1;
if(MN>0) then
begin
FOR SELECT
    ISLEM_KOD,URUN_KODU,URUN_ADI,U_ADI_2,U_ADI_3,U_ADI_4,URUN_TURU,SATIS_KOD,MASA_NO,MASA,ADET,BIRIM_FIYATI,TUTAR,
    ISLEM,KULLANICI,TARIH,SAAT, HESAP,TAHSILAT,YAZICI_GRUBU,CARI_KODU,CARI,KASA,PAKET,
    TAH_KOD
    FROM  DATA
    where islem=1 and MASA_NO=:MN and urun_turu in (0,2,3)
INTO
    :ISLEM_KOD,:URUN_KODU,:URUN_ADI,:U_ADI_2,:U_ADI_3,:U_ADI_4,:URUN_TURU,:SATIS_KOD,:MASA_NO,:MASA,:ADET,:BIRIM_FIYATI,:TUTAR,
    :ISLEM,:KULLANICI,:TARIH,:SAAT,:HESAP,:TAHSILAT,:YAZICI_GRUBU,:CARI_KODU,:CARI,:KASA,:PAKET,
    :TAH_KOD
DO
BEGIN
     MYSTR='';
     ACIKLAMA='';
     MYVAR=0;
     TOPLAM=0;

     FOR SELECT distinct urun_kodu, TUTAR, URUN_ADI FROM  DATA where URUN_TURU=1 AND SATIS_KOD=:ISLEM_KOD
     INTO :temp1, :MYVAR,:MYSTR
     DO
     begin
     TOPLAM=TOPLAM+MYVAR;
     ACIKLAMA=ACIKLAMA || :MYSTR || ',';
     end
     SUSPEND;
     SON=0;
END
if(SON=1) then
SUSPEND ;
end

ELSE if(MN=0) then
begin
FOR SELECT
    ISLEM_KOD,URUN_KODU,URUN_ADI,U_ADI_2,U_ADI_3,U_ADI_4,URUN_TURU,SATIS_KOD,MASA_NO,MASA,ADET,BIRIM_FIYATI,TUTAR,
    ISLEM,KULLANICI,TARIH,SAAT, HESAP,TAHSILAT,YAZICI_GRUBU,CARI_KODU,CARI,KASA,PAKET,
    TAH_KOD
    FROM  DATA
    where urun_turu in (0,2,3) and adet > 0 AND (ISLEM IS NULL OR ISLEM=-1)
INTO
    :ISLEM_KOD,:URUN_KODU,:URUN_ADI,:U_ADI_2,:U_ADI_3,:U_ADI_4,:URUN_TURU,:SATIS_KOD,:MASA_NO,:MASA,:ADET,:BIRIM_FIYATI,:TUTAR,
    :ISLEM,:KULLANICI,:TARIH,:SAAT,:HESAP,:TAHSILAT,:YAZICI_GRUBU,:CARI_KODU,:CARI,:KASA,:PAKET,
    :TAH_KOD
DO
BEGIN
     MYSTR='';
     ACIKLAMA='';
     MYVAR=0;
     TOPLAM=0;

     FOR SELECT distinct urun_kodu, TUTAR, URUN_ADI FROM  DATA where URUN_TURU=1 AND SATIS_KOD=:ISLEM_KOD
     INTO :temp1, :MYVAR,:MYSTR
     DO
     begin
     TOPLAM=TOPLAM+MYVAR;
     ACIKLAMA=ACIKLAMA || :MYSTR || ',';
     end
     SUSPEND;
     SON=0;
END
if(SON=1) then
SUSPEND ;
end
END
;@






CREATE PROCEDURE P_KASA (
    TARIH DATE,
    SUBE  INTEGER)
RETURNS (
    URUN_TURU SMALLINT,
    ALAN VARCHAR (60) CHARACTER SET WIN1254,
    TUTAR DOUBLE PRECISION)
AS
/**/
BEGIN


FOR
select   CAST(11 as smallint),  KASA_ISMI
from KASALAR
where    ISLEM=1
INTO     :URUN_TURU,:ALAN DO SUSPEND;

FOR
select   CAST(11 as smallint),  KASA,  SUM(TUTAR)
from YEDEK_RAPOR
where    URUN_TURU=11 and TARIH=:TARIH and SUBE_ID=:sube
GROUP BY KASA
INTO     :URUN_TURU,:ALAN,:TUTAR DO SUSPEND;

FOR
select   CAST(12 as smallint),  CAST('Giren Depozito' as VARCHAR (60) CHARACTER SET WIN1254), SUM(TUTAR)
from YEDEK_RAPOR
where    URUN_TURU=12 and TARIH=:TARIH and SUBE_ID=:sube
INTO     :URUN_TURU,:ALAN,:TUTAR DO SUSPEND;

FOR
select   CAST(13 as smallint),  CAST('kan Depozito' as VARCHAR (60) CHARACTER SET WIN1254), SUM(TUTAR)
from YEDEK_RAPOR
where    URUN_TURU=13 and TARIH=:TARIH and SUBE_ID=:sube
INTO     :URUN_TURU,:ALAN,:TUTAR DO SUSPEND;

FOR
select   CAST(14 as smallint),  CAST('Banka' as VARCHAR (60) CHARACTER SET WIN1254), SUM(TUTAR)
from YEDEK_RAPOR
where    URUN_TURU=14 and TARIH=:TARIH and SUBE_ID=:sube
INTO     :URUN_TURU,:ALAN,:TUTAR DO SUSPEND;

FOR
select   CAST(15 as smallint) A,  CAST('Masraflar' as VARCHAR (60) CHARACTER SET WIN1254), SUM(TUTAR)
from YEDEK_RAPOR
where    URUN_TURU=15 and TARIH=:TARIH and SUBE_ID=:sube
INTO     :URUN_TURU,:ALAN,:TUTAR DO SUSPEND;

FOR
select   CAST(16 as smallint) A,  CAST('Ciro' as VARCHAR (60) CHARACTER SET WIN1254), SUM(TUTAR)
from YEDEK_RAPOR 
where    (URUN_TURU between 11 and 19)  and TARIH=:TARIH and SUBE_ID=:sube
INTO     :URUN_TURU,:ALAN,:TUTAR DO SUSPEND;

END
;@




CREATE PROCEDURE P_KURYE (
    TARIH DATE,
    KURYE VARCHAR (50) CHARACTER SET WIN1254)
RETURNS (
    CARI_KODU INTEGER,
    ISIM VARCHAR (50) CHARACTER SET WIN1254,
    KASA VARCHAR (50) CHARACTER SET WIN1254,
    TOPLAM DOUBLE PRECISION,
    TAHSILAT DOUBLE PRECISION)
AS
BEGIN
if(:KURYE='') then
begin
     FOR
     select
     CARI_KODU,
     ISIM,
     CAST(0 as DOUBLE PRECISION),
     CAST(0 as DOUBLE PRECISION)
     FROM  CARILER where KART_TURU='Kurye'
     
     union

     select
     CAST(0 as INTEGER),
     KULLANICI,
     SUM(TUTAR),
     CAST(0 as DOUBLE PRECISION)
     FROM  YEDEK_RAPOR
     where MASA_NO=251 AND (urun_turu between 1 and 3) and masa='Kurye' and TARIH=:TARIH
     GROUP BY KULLANICI
  
     union

     select
     CAST(0 as INTEGER),
     KULLANICI,
     CAST(0 as DOUBLE PRECISION),
     SUM(TUTAR)
     FROM  YEDEK_RAPOR
     where MASA_NO=251 AND (urun_turu between 5 and 5) and masa='Kurye' and TARIH=:TARIH
     GROUP BY KULLANICI
     INTO
     :CARI_KODU,
     :ISIM,
     :TOPLAM,
     :TAHSILAT
     DO
     SUSPEND ;
end

else
begin
     FOR
     select
     KASA,
     SUM(TUTAR),
     CAST(0 as DOUBLE PRECISION)
     FROM  YEDEK_RAPOR
     where MASA_NO=251 AND (urun_turu between 1 and 3) and masa='Kurye' and TARIH=:TARIH AND KULLANICI=:KURYE
     GROUP BY KASA

     union

     select
     KASA,
     CAST(0 as DOUBLE PRECISION),
     SUM(TUTAR)
     FROM  YEDEK_RAPOR
     where MASA_NO=251 AND (urun_turu between 5 and 5) and masa='Kurye' and TARIH=:TARIH AND KULLANICI=:KURYE
     GROUP BY KASA
     INTO
     :KASA,
     :TOPLAM,
     :TAHSILAT
     DO
     SUSPEND ;
end
END
;@



CREATE PROCEDURE P_PDA 
RETURNS (
    GRUP3 VARCHAR (25) CHARACTER SET WIN1254,
    PLU_NO INTEGER,
    URUN_KODU NUMERIC (18, 0),
    URUN_ADI VARCHAR (100) CHARACTER SET WIN1254,
    A DOUBLE PRECISION,
    B DOUBLE PRECISION,
    C DOUBLE PRECISION,
    D DOUBLE PRECISION,
    E DOUBLE PRECISION,
    F DOUBLE PRECISION,
    OZEL_MENU SMALLINT,
    PDA VARCHAR (4096) CHARACTER SET WIN1254)
AS/**/
DECLARE VARIABLE ACIKLAMA VARCHAR (255) CHARACTER SET WIN1254;
DECLARE VARIABLE MYSTR  VARCHAR (4096) CHARACTER SET WIN1254;
DECLARE VARIABLE S  VARCHAR (4096) CHARACTER SET WIN1254;
DECLARE VARIABLE SON SMALLINT;
BEGIN

SON=1;
FOR SELECT
    GRUP3,PLU_NO,URUN_KODU,URUN_ADI,A,B,C,D,E,F,OZEL_MENU,ACIKLAMA
    FROM  URUNLER
    WHERE (urun_turu between 1 and 2) and
          URUN_KODU < 1211 and
          URUN_ADI is not null and URUN_ADI NOT IN ('', ' ','X') and
          GRUP3 is not null and GRUP3 NOT IN ('', ' ', 'X')
    ORDER BY GRUP3,TUS
INTO
    :GRUP3,:PLU_NO,:URUN_KODU,:URUN_ADI,:A,:B,:C,:D,:E,:F,:OZEL_MENU,:ACIKLAMA
DO
  BEGIN
     MYSTR='';
     PDA='';
     if(:ACIKLAMA IS NULL or :ACIKLAMA='') then
       S='SELECT URUN_ADI FROM URUNLER where URUN_TURU=1 AND urun_kodu in(0)';
     else
       S='SELECT URUN_ADI FROM URUNLER where URUN_TURU=1 AND urun_kodu in(' || :ACIKLAMA || ') and lower(urun_adi) not like ''%(fix)%''';

     FOR EXECUTE STATEMENT S
     INTO :MYSTR
     DO
     begin
     PDA=PDA || :MYSTR || ',';
     end
     SUSPEND;
     SON=0;
  END
if(SON=1) then
SUSPEND ;
END
;@




CREATE PROCEDURE P_PLU_NO 
AS
DECLARE VARIABLE P DOUBLE PRECISION;
BEGIN
  FOR
    SELECT urun_kodu FROM URUNLER 
  INTO :P
  DO
  BEGIN
     IF(P<2000) THEN
     UPDATE URUNLER SET plu_no=:P where urun_kodu=:P;
     else
     UPDATE URUNLER SET plu_no=GEN_ID(gen_plu,1) where urun_kodu=:P;
  END
  SUSPEND;
END
;@





CREATE PROCEDURE P_SON_ISLEM (
    GST DATE)
RETURNS (
    TAH_KOD INTEGER,
    URUN_KODU NUMERIC (18, 0),
    PLU_NO INTEGER,
    URUN_ADI VARCHAR (100) CHARACTER SET WIN1254,
    MASA_NO INTEGER,
    MASA VARCHAR (25) CHARACTER SET WIN1254,
    KULLANICI VARCHAR (50) CHARACTER SET WIN1254,
    TARIH DATE,
    SAAT TIME,
    KASA VARCHAR (50) CHARACTER SET WIN1254,
    ADET DOUBLE PRECISION,
    TUTAR DOUBLE PRECISION)
AS/**/
BEGIN

FOR SELECT
    TAH_KOD,URUN_KODU,PLU_NO,URUN_ADI,
    MASA_NO,MASA,KULLANICI,TARIH,SAAT,KASA,ADET,TUTAR
    FROM  yedek_rapor
    where TARIH=:GST and islem=2 and (URUN_TURU between 0 and 3) and (URUN_KODU not between 911 and 1210)
INTO
    :TAH_KOD,:URUN_KODU,:PLU_NO,:URUN_ADI,
    :MASA_NO,:MASA,:KULLANICI,:TARIH,:SAAT,:KASA,:ADET,:TUTAR
DO
SUSPEND ;

FOR SELECT
    TAH_KOD,URUN_KODU,PLU_NO,CAST('eni Toplami' as VARCHAR (100) CHARACTER SET WIN1254),
    MASA_NO,MASA,KULLANICI,TARIH,SAAT,KASA,ADET,TUTAR
    FROM  yedek_rapor
    where TARIH=:GST and islem=2 and (URUN_KODU between 911 and 1210) and adet > 0
INTO
    :TAH_KOD,:URUN_KODU,:PLU_NO,:URUN_ADI,
    :MASA_NO,:MASA,:KULLANICI,:TARIH,:SAAT,:KASA,:ADET,:TUTAR
DO
SUSPEND ;

END
;@





CREATE PROCEDURE RP_2 (
    TARIH DATE)
RETURNS (
    A VARCHAR (2) CHARACTER SET WIN1254,
    ALAN VARCHAR (60) CHARACTER SET WIN1254,
    TUTAR DOUBLE PRECISION)
AS
BEGIN
FOR
select '01' A, 'Normal Sat            ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 1 and 2) and adet > 0 and MASA not in('Veresiye','Kurye','Market')
union
select '02' A, 'Veresiye Sat          ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 1 and 2) and adet > 0 and MASA = 'Veresiye'
union
select '03' A, 'Kurye                   ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 1 and 2) and adet > 0 and MASA = 'Kurye'
union
select '03' A, 'Market Sat            ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 1 and 1) and adet > 0 and MASA = 'Market'
union
select '04' A, 'Brt Sat TOPLAMI     ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 1 and 2) and adet > 0
union
select '08' A, 'ndirimler TOPLAMI      ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 3 and 3)
union
select '09' A, 'Net Sati TOPLAMI       ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 1 and 3)  and adet > 0
union
select '10' A, 'Direk Para Girii       ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 4 and 4)  and TUTAR > 0
union
select '11' A, 'Direk Para k       ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 4 and 4)  and TUTAR < 0
union
select '12' A, 'Veresiye Tahsilat      ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 5 and 5) and MASA = 'Veresiye'
union
select '12' A, 'Kuryeden Tahsilat       ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 5 and 5) and MASA = 'Kurye'
union
select '13' A, 'Normal Tahsilat         ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 6 and 6)
union
select '14' A, 'Market Tahsilat         ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 7 and 7)
union
select '15' A, 'Net Tahsilat TOPLAMI    ' ALAN, sum(TUTAR) TUTAR from YEDEK_RAPOR
   where TARIH=:TARIH and (URUN_TURU between 4 and 7)

    INTO :A,:ALAN,:TUTAR
    DO
    SUSPEND ;
END
;@




CREATE PROCEDURE R_DEPO (
    URUN_KODU NUMERIC (13, 0))
RETURNS (
    ISIM VARCHAR (50) CHARACTER SET WIN1254,
    MIKTAR DOUBLE PRECISION)
AS
BEGIN
FOR SELECT
    B.ISIM,
    SUM(A.MIKTAR)
    FROM STOK A, CARILER B
    WHERE URUN_KODU=:URUN_KODU AND B.KART_TURU='Sube' AND ISLEM_TIPI IN(2003,-2003,-2004) AND A.CARI_KODU=B.CARI_KODU
    GROUP BY A.URUN_KODU, B.ISIM
INTO :ISIM,:MIKTAR DO SUSPEND ;

FOR SELECT
    B.ISIM,
    SUM(A.MIKTAR)*-1
    FROM STOK A, CARILER B
    WHERE URUN_KODU=:URUN_KODU AND B.KART_TURU='Sube' AND ISLEM_TIPI IN(-21,21,22) AND A.CARI_KODU=B.CARI_KODU
    GROUP BY A.URUN_KODU, B.ISIM
INTO :ISIM,:MIKTAR DO SUSPEND ;

FOR SELECT
    CAST('MERKEZ' as VARCHAR (50) CHARACTER SET WIN1254),
    SUM(MIKTAR)
    FROM STOK 
    WHERE URUN_KODU=:URUN_KODU AND ISLEM_TIPI IN(-2,2)
INTO :ISIM,:MIKTAR DO SUSPEND ;

FOR SELECT
    ISIM,
    CAST(0 as DOUBLE PRECISION)
    FROM CARILER
    WHERE KART_TURU = 'Sube'
INTO :ISIM,:MIKTAR DO SUSPEND ;

END
;@











CREATE PROCEDURE R_URUN_TOP (
    VERI VARCHAR (25) CHARACTER SET WIN1254,
    YER SMALLINT)
RETURNS (
    MIKTAR DOUBLE PRECISION,
    URUN_KODU NUMERIC (18, 0),
    GRUP1 VARCHAR (50) CHARACTER SET WIN1254,
    GRUP2 VARCHAR (50) CHARACTER SET WIN1254,
    GRUP3 VARCHAR (50) CHARACTER SET WIN1254,
    URUN_ADI VARCHAR (100) CHARACTER SET WIN1254,
    A DOUBLE PRECISION,
    B DOUBLE PRECISION,
    C DOUBLE PRECISION,
    D DOUBLE PRECISION,
    E DOUBLE PRECISION,
    F DOUBLE PRECISION,
    KDV SMALLINT,
    MIKTAR_TURU VARCHAR (25) CHARACTER SET WIN1254,
    X_NO_1 VARCHAR (25) CHARACTER SET WIN1254,
    X_NO_2 VARCHAR (25) CHARACTER SET WIN1254,
    BARKOD_1 VARCHAR (25) CHARACTER SET WIN1254,
    BARKOD_2 VARCHAR (25) CHARACTER SET WIN1254,
    PLU_NO INTEGER )
AS/**/
BEGIN
    A=0;
    B=0;
    C=0;
    D=0;
    E=0;
    F=0;
    KDV=0;

    
FOR SELECT -- FATURALI TUM GR CIKILAR
sum(a.MIKTAR),
a.URUN_KODU,
b.GRUP1,
b.GRUP2,
b.GRUP3,
b.URUN_ADI,
b.MIKTAR_TURU,
b.X_NO_1,
b.X_NO_2,
b.BARKOD_1,
b.BARKOD_2,
a.PLU_NO
FROM STOK a, URUNLER b
WHERE     a.ISLEM_DURUMU=1 AND a.ISLEM_TIPI IN(2,-2,21,-21) AND a.URUN_KODU=b.URUN_KODU
group by  a.URUN_KODU, b.GRUP1, b.GRUP2, b.GRUP3, b.URUN_ADI, b.KDV, b.MIKTAR_TURU,
          b.X_NO_1, b.X_NO_2, b.BARKOD_1, b.BARKOD_2, PLU_NO

  INTO :MIKTAR,:URUN_KODU,:GRUP1,:GRUP2,:GRUP3,:URUN_ADI,:MIKTAR_TURU,
       :X_NO_1,:X_NO_2,:BARKOD_1,:BARKOD_2, :PLU_NO
  DO  SUSPEND ;

FOR SELECT -- SAYIMLAR,RECETE AKTARIMLARI,RESTODAN DREK STOK GR VE PERAKENDE SATISLAR,
sum(a.MIKTAR) ,
a.URUN_KODU,
b.GRUP1,
b.GRUP2,
b.GRUP3,
b.URUN_ADI,
b.MIKTAR_TURU,
b.X_NO_1,
b.X_NO_2,
b.BARKOD_1,
b.BARKOD_2,
a.PLU_NO
FROM STOK a, URUNLER b
WHERE     a.ISLEM_DURUMU=1 AND CARI_KODU=10001 AND a.ISLEM_TIPI IN(-2004,2003,-2003,22) AND a.URUN_KODU=b.URUN_KODU
group by  a.URUN_KODU, b.GRUP1, b.GRUP2, b.GRUP3, b.URUN_ADI, b.KDV, b.MIKTAR_TURU,
          b.X_NO_1, b.X_NO_2, b.BARKOD_1, b.BARKOD_2, PLU_NO

   INTO :MIKTAR,:URUN_KODU,:GRUP1,:GRUP2,:GRUP3,:URUN_ADI,:MIKTAR_TURU,
        :X_NO_1,:X_NO_2,:BARKOD_1,:BARKOD_2, :PLU_NO
   DO  SUSPEND ;

FOR SELECT -- TM RNLER
CAST(0 as DOUBLE PRECISION),
URUN_KODU,
GRUP1,
GRUP2,
GRUP3,
URUN_ADI,
MIKTAR_TURU,
A,
B,
C,
D,
E,
F,
KDV,
X_NO_1,
X_NO_2,
BARKOD_1,
BARKOD_2,
PLU_NO
FROM URUNLER

  INTO :MIKTAR,:URUN_KODU,:GRUP1,:GRUP2,:GRUP3,:URUN_ADI,:MIKTAR_TURU,
       :A,:B,:C,:D,:E,:F,:KDV,:X_NO_1,:X_NO_2,:BARKOD_1,:BARKOD_2, :PLU_NO
  DO  SUSPEND ;
END
;@




CREATE PROCEDURE URUN (
    ARA VARCHAR (100) CHARACTER SET WIN1254,
    KRT SMALLINT)
RETURNS (
    URUN_KODU NUMERIC (13, 0),
    GRUP1 VARCHAR (25) CHARACTER SET WIN1254,
    GRUP2 VARCHAR (25) CHARACTER SET WIN1254,
    GRUP3 VARCHAR (25) CHARACTER SET WIN1254,
    URUN_ADI VARCHAR (100) CHARACTER SET WIN1254,
    A DOUBLE PRECISION,
    B DOUBLE PRECISION,
    C DOUBLE PRECISION,
    D DOUBLE PRECISION,
    E DOUBLE PRECISION,
    F DOUBLE PRECISION,
    INDIRIM SMALLINT,
    KDV SMALLINT,
    PLU_NO INTEGER)
AS
DECLARE VARIABLE MYSTR VARCHAR(250) CHARACTER SET WIN1254;
BEGIN
    MYSTR='SELECT URUN_KODU, GRUP1, GRUP2, GRUP3, URUN_ADI, A,B,C,D,E,F,INDIRIM,KDV,PLU_NO FROM URUNLER';

    if(KRT=0) then
    MYSTR=MYSTR || ' WHERE URUN_KODU=' || :ARA ;
    ELSE if (KRT=1) then
    MYSTR=MYSTR || ' WHERE URUN_ADI=''' || :ARA  || '''';

    FOR EXECUTE STATEMENT MYSTR
    INTO :URUN_KODU,:GRUP1,:GRUP2,:GRUP3,:URUN_ADI,:A,:B,:C,:D,:E,:F,:INDIRIM,:KDV,:PLU_NO
    DO
    SUSPEND ;
END
;@



CREATE PROCEDURE P_MS_YOGUNLUK (
    D1 DATE)
RETURNS (
    MASA_NO INTEGER,
    S INTEGER,
    TAH_KOD INTEGER)
AS
DECLARE VARIABLE S1 INTEGER;
DECLARE VARIABLE S2 INTEGER;
DECLARE VARIABLE M1 INTEGER;
DECLARE VARIABLE T1 INTEGER;
DECLARE VARIABLE CNT INTEGER;
BEGIN

    FOR SELECT
       extract (hour FROM MIN(SAAT)),
       extract (hour FROM MAX(SAAT)),
       TAH_KOD,
       MASA_NO FROM  yedek_rapor A
    where islem=2 AND TARIH=:D1
    GROUP BY TAH_KOD,MASA_NO
    INTO :S1,:S2,:T1,:M1
    DO
    begin
        CNT = S1;
        WHILE (CNT <= S2) DO
          BEGIN
            S=CNT;
            TAH_KOD=t1;
            MASA_NO=M1;
            SUSPEND;
          CNT = CNT + 1;
          END
    end
    
END
;@






CREATE PROCEDURE CARI_TOP_X (
    IN_BR VARCHAR (25) CHARACTER SET WIN1254,
    IN_KART_TURU VARCHAR (25) CHARACTER SET WIN1254,
    IN_KART_DURUMU VARCHAR (25) CHARACTER SET WIN1254,
    IN_SUBE_KODU INTEGER)
RETURNS (
    CARI_KODU INTEGER,
    ISIM VARCHAR (50) CHARACTER SET WIN1254,
    BR VARCHAR (25) CHARACTER SET WIN1254,
    TL_1 DOUBLE PRECISION,
    D1 DOUBLE PRECISION,
    D2 DOUBLE PRECISION,
    D3 DOUBLE PRECISION,
    D4 DOUBLE PRECISION,
    D5 DOUBLE PRECISION)
AS/**/
  DECLARE VARIABLE d11 DOUBLE PRECISION;
  DECLARE VARIABLE d22 DOUBLE PRECISION;
  DECLARE VARIABLE d33 DOUBLE PRECISION;
  DECLARE VARIABLE d44 DOUBLE PRECISION;
  DECLARE VARIABLE d55 DOUBLE PRECISION;
  DECLARE VARIABLE kur DOUBLE PRECISION;
BEGIN

SELECT kur  from doviz_kurlari where SIRA=1  INTO :d11 ;
SELECT kur  from doviz_kurlari where SIRA=2  INTO :d22 ;
SELECT kur  from doviz_kurlari where SIRA=3  INTO :d33 ;
SELECT kur  from doviz_kurlari where SIRA=4  INTO :d44 ;
SELECT kur  from doviz_kurlari where SIRA=5  INTO :d55 ;
SELECT kur  from doviz_kurlari where para=:IN_BR  INTO :kur ;
    
    
IF (IN_KART_TURU <> 'Sube') then
begin
   IF (IN_SUBE_KODU=0) THEN
   BEGIN
      FOR
      SELECT
      a.CARI_KODU,
      b.ISIM,
      :IN_BR,
      Sum((D1*:d11+D2*:d22+D3*:d33+D4*:d44+D5*:d55)/:kur),
      Sum(a.D1),
      Sum(a.D2),
      Sum(a.D3),
      Sum(a.D4),
      Sum(a.D5)
      FROM CK a, CARILER b
      WHERE
      b.KART_TURU=:IN_KART_TURU
      and b.KART_DURUMU=:IN_KART_DURUMU
      and a.CARI_KODU >=10000
      and a.CARI_KODU=b.CARI_KODU
      and a.ISLEM_DURUMU in(1, 2)
      and a.ISLEM_TIPI NOT IN (21,-21,23,24)
      GROUP BY a.CARI_KODU, b.ISIM
      INTO :CARI_KODU, :ISIM, :BR, :TL_1, :D1,:D2,:D3,:D4,:D5  DO  SUSPEND ;
   END

   ELSE
   BEGIN
      FOR
      SELECT
      a.CARI_KODU,
      b.ISIM,
      :IN_BR,
      Sum((D1*:d11+D2*:d22+D3*:d33+D4*:d44+D5*:d55)/:kur),
      Sum(a.D1),
      Sum(a.D2),
      Sum(a.D3),
      Sum(a.D4),
      Sum(a.D5)
      FROM CK a, CARILER b
      WHERE
      a.sube_kodu=:IN_SUBE_KODU
      and b.KART_TURU=:IN_KART_TURU
      and b.KART_DURUMU=:IN_KART_DURUMU
      and a.CARI_KODU >=10000
      and a.CARI_KODU=b.CARI_KODU
      and a.ISLEM_DURUMU in(1, 2)
      and a.ISLEM_TIPI NOT IN (21,-21,23,24)
      GROUP BY a.CARI_KODU, b.ISIM
      INTO :CARI_KODU, :ISIM, :BR, :TL_1, :D1,:D2,:D3,:D4,:D5  DO  SUSPEND ;
   END
end



ELSE IF (IN_KART_TURU='Sube') then
begin
   FOR
   SELECT
      a.CARI_KODU,
      b.ISIM,
      :IN_BR,
      Sum((D1*:d11+D2*:d22+D3*:d33+D4*:d44+D5*:d55)/:kur),
      Sum(a.D1),
      Sum(a.D2),
      Sum(a.D3),
      Sum(a.D4),
      Sum(a.D5)
   FROM CK a, CARILER b
   WHERE
      b.KART_TURU='Sube'
      and a.CARI_KODU >=10000
      and a.CARI_KODU=b.CARI_KODU
      and a.ISLEM_DURUMU in(1, 2)
      and a.ISLEM_TIPI NOT IN (22,23,24)
   GROUP BY a.CARI_KODU, b.ISIM
   INTO :CARI_KODU, :ISIM, :BR, :TL_1, :D1,:D2,:D3,:D4,:D5
   DO
   SUSPEND ;

   FOR
   SELECT
      a.SUBE_KODU,
      b.ISIM,
      :IN_BR,
      Sum((D1*:d11+D2*:d22+D3*:d33+D4*:d44+D5*:d55)/:kur*-1),
      Sum(a.D1*-1),
      Sum(a.D2*-1),
      Sum(a.D3*-1),
      Sum(a.D4*-1),
      Sum(a.D5*-1)
   FROM CK a, CARILER b
   WHERE
      b.KART_TURU='Sube'
      and a.SUBE_KODU >=10000
      and a.SUBE_KODU=b.CARI_KODU
      and a.ISLEM_DURUMU in(1, 2)
      and a.ISLEM_TIPI NOT IN (22,23,24)
   GROUP BY a.SUBE_KODU, b.ISIM
   INTO :CARI_KODU, :ISIM, :BR, :TL_1, :D1,:D2,:D3,:D4,:D5
   DO
   SUSPEND ;
end



FOR    -- Tm cariler
  SELECT
  CARI_KODU,
  ISIM,
  :IN_BR,
  CAST(0 as DOUBLE PRECISION),
  CAST(0 as DOUBLE PRECISION),
  CAST(0 as DOUBLE PRECISION),
  CAST(0 as DOUBLE PRECISION),
  CAST(0 as DOUBLE PRECISION),
  CAST(0 as DOUBLE PRECISION)
  FROM CARILER
  WHERE
  KART_TURU=:IN_KART_TURU
  and KART_DURUMU=:IN_KART_DURUMU 
  and CARI_KODU >=10000
INTO :CARI_KODU, :ISIM, :BR, :TL_1, :D1,:D2,:D3,:D4,:D5  DO  SUSPEND ;

END
;@




